package service

import (
	"context"
	"fmt"
	"regexp"
	"strings"
	"time"

	"ddl_checker/internal/model"
	"ddl_checker/internal/parser"
	"ddl_checker/pkg/database"
)

// ValidationService 验证服务
type ValidationService struct {
	db              *database.DB
	analyzer        *parser.HybridAnalyzer
	schemaService   *SchemaService
	externalChecker *ExternalChecker
}

// NewValidationService 创建验证服务
func NewValidationService(db *database.DB) *ValidationService {
	return &ValidationService{
		db:              db,
		analyzer:        parser.NewHybridAnalyzer(),
		schemaService:   NewSchemaService(db),
		externalChecker: NewExternalChecker(),
	}
}

// ValidateDDL 验证DDL语句
func (s *ValidationService) ValidateDDL(ctx context.Context, req *model.ValidationRequest) (*model.ValidationResponse, error) {
	response := &model.ValidationResponse{
		RequestID:   fmt.Sprintf("req_%d", time.Now().UnixNano()),
		ProcessedAt: time.Now(),
	}

	// 验证DDL语句
	if req.DDL != "" {
		ddlResults, err := s.validateDDLStatements(ctx, req.DDL, req)
		if err != nil {
			return nil, fmt.Errorf("验证DDL失败: %w", err)
		}
		response.DDLResults = ddlResults
		response.Summary.HasDDL = true
		response.Summary.IsDDLValid = s.isResultsValid(ddlResults)
	}

	// 验证Rollback语句
	if req.Rollback != "" {
		rollbackResults, err := s.validateRollbackStatements(ctx, req.Rollback, req)
		if err != nil {
			return nil, fmt.Errorf("验证Rollback失败: %w", err)
		}
		response.RollbackResults = rollbackResults
		response.Summary.HasRollback = true
		response.Summary.IsRollbackValid = s.isResultsValid(rollbackResults)
	}

	// 自动生成Rollback
	if req.AutoGenerateRollback && req.DDL != "" && req.Rollback == "" {
		generatedRollback, err := s.generateRollback(ctx, req.DDL)
		if err != nil {
			return nil, fmt.Errorf("生成Rollback失败: %w", err)
		}
		response.GeneratedRollback = generatedRollback
	}

	// 外部API检查
	if req.EnableExternalCheck && req.ExternalAPIURL != "" {
		externalResult, err := s.externalChecker.Check(ctx, req.ExternalAPIURL, req)
		if err != nil {
			return nil, fmt.Errorf("外部API检查失败: %w", err)
		}
		response.ExternalCheckResult = externalResult
	}

	// 生成摘要
	s.generateSummary(response)

	return response, nil
}

// validateDDLStatements 验证DDL语句
func (s *ValidationService) validateDDLStatements(ctx context.Context, ddl string, req *model.ValidationRequest) ([]model.ValidationResult, error) {
	results := make([]model.ValidationResult, 0)

	// 解析DDL语句
	statements, err := s.analyzer.ParseBatchDDL(ddl, ";")
	if err != nil {
		return nil, fmt.Errorf("解析DDL失败: %w", err)
	}

	// 跨语句注释验证
	commentValidationResults := s.validateCommentsAcrossStatements(statements)

	for i, stmt := range statements {
		result := model.ValidationResult{
			ID:        int64(i + 1),
			SQL:       stmt.OriginalSQL,
			CheckType: "ddl_syntax",
			CreatedAt: time.Now(),
		}

		// 语法验证
		if !stmt.IsValid {
			result.IsValid = false
			result.ErrorType = "syntax_error"
			result.ErrorMessage = stmt.Error
			result.Severity = "error"
			results = append(results, result)
			continue
		}

		// 使用跨语句注释验证结果
		commentErrors := commentValidationResults[i]
		if len(commentErrors) > 0 {
			result.IsValid = false
			result.ErrorType = "missing_comment"
			result.ErrorMessage = "缺少字段描述"
			result.Severity = "warning"
			result.Details.MissingComments = commentErrors
		}

		// 数据库存在性验证
		existenceErrors, err := s.validateExistence(ctx, stmt, req)
		if err != nil {
			return nil, fmt.Errorf("存在性验证失败: %w", err)
		}
		if len(existenceErrors) > 0 {
			result.IsValid = false
			result.ErrorType = "existence_error"
			result.ErrorMessage = "对象已存在"
			result.Severity = "error"
			result.Details.ExistingTables = existenceErrors
		}

		// 范围验证
		scopeErrors := s.validateScope(stmt, req)
		if len(scopeErrors) > 0 {
			result.IsValid = false
			result.ErrorType = "scope_error"
			result.ErrorMessage = "超出允许范围"
			result.Severity = "error"
			result.Details.InvalidObjects = scopeErrors
		}

		// 如果没有错误，则标记为有效
		if len(commentErrors) == 0 && len(existenceErrors) == 0 && len(scopeErrors) == 0 {
			result.IsValid = true
			result.Severity = "info"
		}

		results = append(results, result)
	}

	return results, nil
}

// validateRollbackStatements 验证Rollback语句
func (s *ValidationService) validateRollbackStatements(ctx context.Context, rollback string, req *model.ValidationRequest) ([]model.ValidationResult, error) {
	results := make([]model.ValidationResult, 0)

	// 解析Rollback语句
	statements, err := s.analyzer.ParseBatchDDL(rollback, ";")
	if err != nil {
		return nil, fmt.Errorf("解析Rollback失败: %w", err)
	}

	for i, stmt := range statements {
		result := model.ValidationResult{
			ID:        int64(i + 1),
			SQL:       stmt.OriginalSQL,
			CheckType: "rollback_syntax",
			CreatedAt: time.Now(),
		}

		// 语法验证
		if !stmt.IsValid {
			result.IsValid = false
			result.ErrorType = "syntax_error"
			result.ErrorMessage = stmt.Error
			result.Severity = "error"
			results = append(results, result)
			continue
		}

		// 检查是否为危险操作
		if s.isDangerousOperation(stmt) {
			result.IsValid = false
			result.ErrorType = "dangerous_operation"
			result.ErrorMessage = "Rollback包含危险操作"
			result.Severity = "error"
			result.Details.Warnings = []string{"Rollback语句包含DROP/DELETE/UPDATE等危险操作"}
		}

		// 如果没有错误，则标记为有效
		if result.IsValid {
			result.Severity = "info"
		}

		results = append(results, result)
	}

	return results, nil
}

// validateCommentsAcrossStatements 跨语句验证字段描述
func (s *ValidationService) validateCommentsAcrossStatements(statements []*database.SQLStatement) [][]string {
	results := make([][]string, len(statements))

	// 构建表名到CREATE语句索引的映射
	tableToCreateStmt := make(map[string]int)

	// 首先找到所有CREATE TABLE语句
	for i, stmt := range statements {
		if stmt.StatementType == "CREATESTMT" && len(stmt.Tables) > 0 {
			for _, table := range stmt.Tables {
				tableToCreateStmt[table] = i
			}
		}
	}

	// 收集所有COMMENT语句信息
	commentInfo := make(map[string]map[string]bool) // 表名 -> 列名 -> 是否有注释

	for _, stmt := range statements {
		if stmt.StatementType == "COMMENTSTMT" {
			s.extractCommentInfo(stmt, commentInfo)
		}
	}

	// 验证每个CREATE TABLE语句的注释完整性
	for i, stmt := range statements {
		if stmt.StatementType == "CREATESTMT" {
			results[i] = s.validateCreateStatementComments(stmt, commentInfo)
		} else {
			results[i] = []string{} // 非CREATE语句暂时不验证注释
		}
	}

	return results
}

// extractCommentInfo 从COMMENT语句中提取注释信息
func (s *ValidationService) extractCommentInfo(stmt *database.SQLStatement, commentInfo map[string]map[string]bool) {
	// 解析SQL查找COMMENT语句
	commentPattern := regexp.MustCompile(`COMMENT\s+ON\s+(COLUMN|TABLE)\s+([^\s]+)\s+IS\s+'([^']*)'`)
	matches := commentPattern.FindAllStringSubmatch(stmt.OriginalSQL, -1)

	for _, match := range matches {
		if len(match) >= 3 {
			commentType := match[1]
			objectName := match[2]

			if commentType == "TABLE" {
				// 表注释
				if commentInfo[objectName] == nil {
					commentInfo[objectName] = make(map[string]bool)
				}
				commentInfo[objectName]["__table__"] = true
			} else if commentType == "COLUMN" {
				// 列注释
				parts := strings.Split(objectName, ".")
				if len(parts) == 2 {
					tableName := parts[0]
					columnName := parts[1]
					if commentInfo[tableName] == nil {
						commentInfo[tableName] = make(map[string]bool)
					}
					commentInfo[tableName][columnName] = true
				}
			}
		}
	}
}

// validateCreateStatementComments 验证CREATE TABLE语句的注释完整性
func (s *ValidationService) validateCreateStatementComments(stmt *database.SQLStatement, commentInfo map[string]map[string]bool) []string {
	missingComments := make([]string, 0)

	if len(stmt.Tables) == 0 {
		return missingComments
	}

	tableName := stmt.Tables[0]
	tableComments := commentInfo[tableName]

	// 检查表注释
	if tableComments == nil || !tableComments["__table__"] {
		missingComments = append(missingComments, fmt.Sprintf("表 %s 缺少描述", tableName))
	}

	// 检查列注释
	for _, column := range stmt.Columns {
		if tableComments == nil || !tableComments[column] {
			missingComments = append(missingComments, fmt.Sprintf("列 %s.%s 缺少描述", tableName, column))
		}
	}

	return missingComments
}

// validateColumnComments 验证字段描述（保持向后兼容）
func (s *ValidationService) validateColumnComments(stmt *database.SQLStatement) []string {
	missingComments := make([]string, 0)

	// 只检查CREATE TABLE语句
	if stmt.StatementType != "CREATESTMT" {
		return missingComments
	}

	// 检查是否有COMMENT语句
	hasComments := false
	commentColumns := make(map[string]bool)

	// 解析SQL查找COMMENT语句
	commentPattern := regexp.MustCompile(`COMMENT\s+ON\s+(COLUMN|TABLE)\s+([^\s]+)\s+IS\s+'([^']*)'`)
	matches := commentPattern.FindAllStringSubmatch(stmt.OriginalSQL, -1)

	for _, match := range matches {
		hasComments = true
		if len(match) >= 3 {
			objectName := match[2]
			commentColumns[objectName] = true
		}
	}

	// 如果有表但没有COMMENT语句，则提示缺少描述
	if len(stmt.Tables) > 0 && !hasComments {
		for _, table := range stmt.Tables {
			missingComments = append(missingComments, fmt.Sprintf("表 %s 缺少描述", table))
		}
	}

	// 检查每个列是否有COMMENT
	if len(stmt.Columns) > 0 {
		for _, column := range stmt.Columns {
			columnKey := fmt.Sprintf("%s.%s", stmt.Tables[0], column)
			if !commentColumns[columnKey] {
				missingComments = append(missingComments, fmt.Sprintf("列 %s 缺少描述", columnKey))
			}
		}
	}

	return missingComments
}

// validateExistence 验证数据库对象存在性
func (s *ValidationService) validateExistence(ctx context.Context, stmt *database.SQLStatement, req *model.ValidationRequest) ([]string, error) {
	existingObjects := make([]string, 0)

	// 如果没有schemaService，跳过存在性检查
	if s.schemaService == nil {
		return existingObjects, nil
	}

	// 只检查CREATE TABLE和ALTER TABLE ADD COLUMN语句
	if stmt.StatementType != "CREATESTMT" && !strings.Contains(stmt.OriginalSQL, "ADD COLUMN") {
		return existingObjects, nil
	}

	// 检查表是否存在
	for _, table := range stmt.Tables {
		schema, tableName := s.parseSchemaAndTable(table)
		exists, err := s.schemaService.ValidateTableExists(ctx, schema, tableName)
		if err != nil {
			return nil, fmt.Errorf("检查表存在性失败: %w", err)
		}
		if exists {
			existingObjects = append(existingObjects, fmt.Sprintf("%s.%s", schema, tableName))
		}
	}

	// 检查列是否存在（ALTER TABLE ADD COLUMN）
	if strings.Contains(stmt.OriginalSQL, "ADD COLUMN") {
		for _, column := range stmt.Columns {
			if len(stmt.Tables) > 0 {
				schema, tableName := s.parseSchemaAndTable(stmt.Tables[0])
				exists, err := s.schemaService.ValidateColumnExists(ctx, schema, tableName, column)
				if err != nil {
					return nil, fmt.Errorf("检查列存在性失败: %w", err)
				}
				if exists {
					existingObjects = append(existingObjects, fmt.Sprintf("%s.%s.%s", schema, tableName, column))
				}
			}
		}
	}

	return existingObjects, nil
}

// validateScope 验证操作范围
func (s *ValidationService) validateScope(stmt *database.SQLStatement, req *model.ValidationRequest) []string {
	invalidObjects := make([]string, 0)

	// 如果没有配置允许范围，则跳过检查
	if len(req.AllowedSchemas) == 0 && len(req.AllowedTables) == 0 {
		return invalidObjects
	}

	// 检查表是否在允许范围内
	for _, table := range stmt.Tables {
		schema, tableName := s.parseSchemaAndTable(table)

		// 检查Schema是否允许
		if len(req.AllowedSchemas) > 0 && !s.contains(req.AllowedSchemas, schema) {
			invalidObjects = append(invalidObjects, fmt.Sprintf("Schema %s 不在允许范围内", schema))
			continue
		}

		// 检查表是否允许
		if len(req.AllowedTables) > 0 && !s.contains(req.AllowedTables, tableName) {
			invalidObjects = append(invalidObjects, fmt.Sprintf("表 %s 不在允许范围内", tableName))
		}
	}

	return invalidObjects
}

// isDangerousOperation 检查是否为危险操作
func (s *ValidationService) isDangerousOperation(stmt *database.SQLStatement) bool {
	upperSQL := strings.ToUpper(stmt.OriginalSQL)

	// 检查危险关键字
	dangerousKeywords := []string{"DROP ", "DELETE ", "UPDATE ", "TRUNCATE "}
	for _, keyword := range dangerousKeywords {
		if strings.Contains(upperSQL, keyword) {
			return true
		}
	}

	return false
}

// generateRollback 生成Rollback语句
func (s *ValidationService) generateRollback(ctx context.Context, ddl string) (string, error) {
	statements, err := s.analyzer.ParseBatchDDL(ddl, ";")
	if err != nil {
		return "", fmt.Errorf("解析DDL失败: %w", err)
	}

	var rollbackSQLs []string

	for _, stmt := range statements {
		switch stmt.StatementType {
		case "CREATESTMT":
			// CREATE TABLE的Rollback是DROP TABLE
			for _, table := range stmt.Tables {
				rollbackSQLs = append(rollbackSQLs, fmt.Sprintf("DROP TABLE IF EXISTS %s;", table))
			}
		case "ALTERTABLESTMT":
			// ALTER TABLE的Rollback需要更复杂的处理
			if strings.Contains(strings.ToUpper(stmt.OriginalSQL), "ADD COLUMN") {
				// ADD COLUMN的Rollback是DROP COLUMN
				for _, column := range stmt.Columns {
					if len(stmt.Tables) > 0 {
						rollbackSQLs = append(rollbackSQLs, fmt.Sprintf("ALTER TABLE %s DROP COLUMN %s;", stmt.Tables[0], column))
					}
				}
			}
		}
	}

	return strings.Join(rollbackSQLs, "\n"), nil
}

// parseSchemaAndTable 解析Schema和表名
func (s *ValidationService) parseSchemaAndTable(fullName string) (schema, table string) {
	parts := strings.Split(fullName, ".")
	if len(parts) == 2 {
		return parts[0], parts[1]
	}
	return "public", fullName
}

// contains 检查字符串是否在切片中
func (s *ValidationService) contains(slice []string, item string) bool {
	for _, s := range slice {
		if s == item {
			return true
		}
	}
	return false
}

// isResultsValid 检查结果是否有效
func (s *ValidationService) isResultsValid(results []model.ValidationResult) bool {
	for _, result := range results {
		if !result.IsValid {
			return false
		}
	}
	return len(results) > 0
}

// generateSummary 生成验证摘要
func (s *ValidationService) generateSummary(response *model.ValidationResponse) {
	allResults := append(response.DDLResults, response.RollbackResults...)

	for _, result := range allResults {
		response.Summary.TotalChecks++

		if result.IsValid {
			response.Summary.PassedChecks++
		} else {
			response.Summary.FailedChecks++

			if result.Severity == "error" {
				response.Summary.ErrorCount++
			} else if result.Severity == "warning" {
				response.Summary.WarningCount++
				response.Summary.WarningChecks++
			}
		}
	}

	// 确定整体状态
	if response.Summary.ErrorCount > 0 {
		response.Summary.OverallStatus = "invalid"
	} else if response.Summary.WarningCount > 0 {
		response.Summary.OverallStatus = "warning"
	} else {
		response.Summary.OverallStatus = "valid"
	}
}
